package day20180408;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class oneKeyTest4 {
    public static void main(String[] args) {

        class Major {
            int id;
            String department;
            String departmentdm;
            String major;
            String majordm;
            int grade;          //班级不能用class

            public Major(int id, String department, String departmentdm, String major, String majordm, int grade) {
                this.id = id;
                this.department = department;
                this.departmentdm = departmentdm;
                this.major = major;
                this.majordm = majordm;
                this.grade = grade;
            }
        }

        class Student {
            int id;
            String name;
            int sex;
            float score;
            String lqzydm;
            String place;

            public Student(int id, String name, int sex, float score, String lqzydm, String place) {
                this.id = id;
                this.name = name;
                this.sex = sex;
                this.score = score;
                this.lqzydm = lqzydm;
                this.place = place;
            }

            public Student(int id) {
                this.id = id;
            }
        }


        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        //查询专业信息
        String sql = "SELECT * from department";
        //查询高考学生信息
        String sql1 = "select * from exam order by score DESC";


        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection(
                    "jdbc:mysql://120.78.82.65/yingxin?useUnicode=true&characterEncoding=UTF-8",
                    "automannn",
                    "chenkaihai...11"
            );
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            //实体类list代表学院专业信息,通过循环将专业信息添加进实体类list
            List<Major> majorList = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String department = resultSet.getString(2);
                String departmentdm = resultSet.getString(3);
                String major = resultSet.getString(4);
                String majordm = resultSet.getString(5);
                int grade = resultSet.getInt(6);

                Major major1 = new Major(id, department, departmentdm, major, majordm, grade);
                majorList.add(major1);
            }

            //查询学生高考信息
            resultSet = statement.executeQuery(sql1);
            //建立集合studentList存放学生信息
            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                int sex = resultSet.getInt(3);
                float score = resultSet.getFloat(4);
                String lqzydm = resultSet.getString(5);
                String place = resultSet.getString(6);

                Student students = new Student(id, name, sex, score, lqzydm, place);
                studentList.add(students);

            }
//
//            //将学生信息插入class表中
//            for (int i = 0;i < studentList.size();i++) {
//
//                String sqlUpdate = "insert into class(name,sex,score,majordm,place,examid) values('"+ studentList.get(i).name +"'," +
//                        "'"+ studentList.get(i).sex +"','"+ studentList.get(i).score +"'," +
//                        "'"+studentList.get(i).lqzydm+"','"+ studentList.get(i).place +"','"+ studentList.get(i).id +"')";
//
//                statement.execute(sqlUpdate);
//            }

            //同专业分班分学号
            for (int i = 0; i < majorList.size(); i++) {
                //建立两个实体类，list1装男生信息，list2装女生信息
                List<Student> list1 = new ArrayList<>();
                List<Student> list2 = new ArrayList<>();
                for (Student e : studentList) {
                    if (e.lqzydm.equals(majorList.get(i).majordm)) {
                        if (e.sex == 1) {
                            System.out.println(e.id + "男");
                            Student student1 = new Student(e.id, e.name, e.sex, e.score, e.lqzydm, e.place);
                            list1.add(student1);
                        } else if (e.sex == 2) {
                            System.out.println(e.id + "女");
                            Student student2 = new Student(e.id, e.name, e.sex, e.score, e.lqzydm, e.place);
                            list2.add(student2);
                        }
                    }
                }

                //按照成绩排序取余，照余数进行分班
                for (int j = 0; j < list1.size(); j++) {
                    int k = j % majorList.get(i).grade + 1;
                    System.out.println(k);
                    String sql2 = "UPDATE class SET class = '" + k + "' WHERE examid = '" + list1.get(j).id + "'";
                    statement = connection.createStatement();
                    statement.execute(sql2);
                }
                System.out.println("\n");


                for (int j = 0; j < list2.size(); j++) {
                    int k = majorList.get(i).grade - (j % majorList.get(i).grade);
                    System.out.println(k);
                    String sql3 = "update class set class = '" + k + "' where examid = '" + list2.get(j).id + "'";
                    statement = connection.createStatement();
                    statement.execute(sql3);
                }
                System.out.println("\n");

//
//                        //按照班级分学号
//                        String sql4 = "select * from class order by class";
//                        resultSet = statement.executeQuery(sql4);
//
//                        //建立一个班级类装入学生
//                        List<Student> classList = new ArrayList<>();
//                        while (resultSet.next()) {
//
//                            int id = resultSet.getInt(1);
//                            Student student = new Student(id);
//                            classList.add(student);
//                        }
//
//                        //学号
//                        List<String> sno = new ArrayList<>();
//                        sno.add("18");                          //设定年
//                        sno.add(majorList.get(i).majordm);      //专业代码
//                        sno.add("001");
//                        StringBuffer stringBuffer = new StringBuffer(sno.get(0));
//                        stringBuffer.append(sno.get(1));
//                        stringBuffer.append(sno.get(2));
//                        for (int j = 0; j < classList.size(); j++) {
//
//                            int k = Integer.valueOf(stringBuffer.toString()) + j;
//                            String sql5 = "update class set sno = '" + k + "' where id = '" + classList.get(j).id + "'";
//                            statement.execute(sql5);
//
//                        }
            }

            //同学院班级排序
            class Department {
                String departmentdm;
                String majordm;
                int classnum;

                public Department(String departmentdm, String majordm, int classnum) {
                    this.departmentdm = departmentdm;
                    this.majordm = majordm;
                    this.classnum = classnum;
                }
            }
            List<Department> departmentList = new ArrayList<>();
            String sqlDepartment = "select * from department order by majordm";
            resultSet = statement.executeQuery(sqlDepartment);
            while(resultSet.next()) {
                String departmentdm = resultSet.getString(3);
                String majordm = resultSet.getString(5);
                int classnum = resultSet.getInt(6);

                Department department = new Department(departmentdm,majordm,classnum);
                departmentList.add(department);
            }
            List<String> departmentdmList = new ArrayList<>();
            String sqlDepartmentdm = "select DISTINCT departmentdm from department ";
            resultSet = statement.executeQuery(sqlDepartmentdm);
            while(resultSet.next()) {
                System.out.println(resultSet.getString(1));
                departmentdmList.add(resultSet.getString(1));
            }
            for (int i = 0;i < departmentdmList.size();i++) {
                int temp = 0;
                for (int j = 0 ;j < departmentList.size();j++) {
                    if (departmentList.get(j).departmentdm.equals(departmentdmList.get(i))) {
                        List<Integer> integerList = new ArrayList<>();
                        resultSet = statement.executeQuery("select class from class where majordm = '"+ departmentList.get(j).majordm +"'");
                        while(resultSet.next()) {
                            System.out.println(resultSet.getInt(1));
                            integerList.add(resultSet.getInt(1));
                        }
                        for (int k = 0;k < integerList.size();k++) {
                            int m = integerList.get(k) + temp;
                            String sql5 = "update class set class = '"+ m +"' where class = '"+ integerList.get(k) +"'";
                            statement.execute(sql5);
                            temp = temp + departmentList.get(j).classnum;
                        }
                    }
                }
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}
